﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using BioModel;
using System.Data.SqlClient;

namespace BioDAL
{
    [DataContract]
    public class BetalingDAL : IBetalingDAL
    {
        #region Connection
        private SqlConnection sqlCn = null;

        public void OpenConnection(string connectionString)
        {
            Console.WriteLine("Connection Established");
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()
        {
            sqlCn.Close();
        }
        #endregion

        #region GetMaxID
        public int GetMaxId(string kolonneNavn, string tabel)
        {
            try
            {
                int mId = -1;
                string sql = "Select MAX(" + kolonneNavn + ") as maxID from " + tabel;
                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    SqlDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        Console.WriteLine(String.Format("{0}", dr["maxID"]));
                        mId = (int)(dr["maxID"]);
                    }
                    dr.Close();
                }
                return mId;
            }
            catch (SqlException se2)
            {
                Console.WriteLine("!SQL fejl " + se2);
                return -1;
            }
        }
        #endregion

        #region Betaling
        public int InsertBetaling(DateTime betalingsDato, bool betalingsStatus)
        {
            try
            {
                string sql = string.Format("set dateformat dmy Insert into bioBetaling" +
                     "(betalingsDato, betalingsStatus) Values" +
                     "('{0}', '{1}')", betalingsDato, betalingsStatus);

                using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException se)
            {
                Console.Write("!SQL Insert betaling fejl " + se);

            }
            return GetMaxId("betalingsId", "bioBetaling");
        }

        public void EditBetaling(int betalingsId, DateTime betalingsDato, bool betalingsStatus)
        {
            string sql = string.Format("set dateformat dmy Update bioBetaling Set betalingsDato = '{0}', betalingsStatus = '{1}' Where betalingsId = {2}"
                , betalingsDato, betalingsStatus, betalingsId);
            Console.WriteLine(sql);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                cmd.ExecuteNonQuery();
            }
        }

        public List<Betaling> AlleBetalinger()
        {
            List<Betaling> inv = new List<Betaling>();
            string sql = string.Format("Select * From bioBetaling");
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    inv.Add(BuildBetaling(dr));

                }
                dr.Close();
            }
            return inv;
        }

        public Betaling GetEnBetaling(int betalingsId)
        {
            Betaling betaling = new Betaling();
            string sql = string.Format("Select * from bioBetaling where betalingsId = {0}", betalingsId);
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    betaling = BuildBetaling(dr);
                }
                dr.Close();
            }
            return betaling;
        }

        private static Betaling BuildBetaling(SqlDataReader dr)
        {
            return new Betaling(
                                    (int)(dr["betalingsId"]),
                                    (DateTime)(dr["betalingsDato"]),
                                    (bool)(dr["betalingsStatus"])
                                    );
        }
        #endregion
    }
}
